package hibernate.hibernateInterface;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Expression;

import hibernate.p.*;
import hibernate.roomSystem.Room;
import hibernate.administratorAccount.*;


//all test cases passed
public class EmployeeSystemHibernateInterface {
	
	public static List getEmployeesByName(String firstName,String middleName,String lastName,String matchCode,Session session)throws Exception
	{
		String sqlScript = "from Employee e,EmployeeInfo ei where e.id = ei.id and ";
		String firstNameScript = "";
		String middleNameScript = "";
		String lastNameScript = "";
		if (matchCode.charAt(0) == '1')
		{
			if (Integer.parseInt(matchCode) != 100)
				firstNameScript = "ei.firstName = '" + firstName + "' and ";
			else
				firstNameScript = "ei.firstName = '" + firstName+"'";
		}
		
		if (matchCode.charAt(1) == '1')
		{
			if (matchCode.charAt(2) == '1')
				middleNameScript = "ei.middleName = '" + middleName + "' and ";
			else
				middleNameScript = "ei.middleName = '" + middleName+"'";
		}
		if (matchCode.charAt(2) == '1')
			lastNameScript = "ei.lastName = '" + lastName+"'";
		sqlScript += firstNameScript + middleNameScript + lastNameScript;

		Query query = session.createQuery(sqlScript);
		List resultList = query.list();
		return resultList;
	}
	
	public static List getEmployeesByMonthlyPay(Pair monthlyPay,int compareScript,Session session)throws Exception
	{
		Double usMonthly = CurrencyExchange.currencyEx((Double)monthlyPay.getFirst(),(Unit)monthlyPay.getSecond());  
		String queryScript = "from Employee e,Salary s where e.id=s.id and s.monthlyPay " + CompareCond.parseCompareScript(compareScript) + usMonthly;

		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		if (resultList.size() == 0)
			throw new Exception("EmployeeNotFoundException");
		return resultList;
	}
	
	public static List getEmployeesByYearlyPay(Pair yearlyPay,int compareScript,Session session)throws Exception
	{
		Double usYearly = CurrencyExchange.currencyEx((Double)yearlyPay.getFirst(),(Unit)yearlyPay.getSecond());  
		String queryScript = "from Employee e,Salary s where e.id=s.id and s.yearlyPay " + CompareCond.parseCompareScript(compareScript) + usYearly;

		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		if (resultList.size() == 0)
			throw new Exception("EmployeeNotFoundException");
		return resultList;
	}
	
	public static Employee getEmployeeByUsername(String username ,Class employeeClass,Session session)throws Exception
	{
		Employee employee;
		String queryScript = "from "+employeeClass.getName()+" e,EmployeeInfo ei where e.id=ei.id and ei.userName='" + username+"'";
	
		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		if (resultList.size() == 0)
			throw new Exception("EmployeeNotFoundException");
		else if (resultList.size() > 1)
			throw new Exception("EmployeeOverlappingException");
		if (employeeClass.equals(FrontDesk.class))
			employee = (FrontDesk)resultList.get(0);
		else if (employeeClass.equals(Manager.class))
			employee = (Manager)resultList.get(0);
		else
			employee = (SoftwareEngineer)resultList.get(0);
		return employee;
	}
	
	public static List getEmployeesByAge(int age,int compareScript,Session session)throws Exception
	{
		String queryScript = "from Employee e,EmployeeInfo ei where e.id = ei.id and ei.age" + CompareCond.parseCompareScript(compareScript) + age;

		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		if (resultList.size() == 0)
			throw new Exception("EmployeeNotFoundException");
		return resultList;
	}
	
	//first stage debugged
	public static List getEmployeesBySex(boolean sex,Session session)throws Exception
	{
		String queryScript = "from Employee e,EmployeeInfo ei where e.id=ei.id and ei.sex=" + sex;

		Query query = session.createQuery(queryScript);
		List resultList = query.list();
		if (resultList.size() == 0)
			throw new Exception("EmployeeNotFoundException");
		return resultList;
	}
	
	private static SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	public static List getEmployeesByBirthday(Date birthdayFrom,Date birthdayTo,Session session)throws Exception
	{
		String from=format.format(birthdayFrom);
		String to=format.format(birthdayTo);
		String queryScript = "from Employee e,EmployeeInfo ei where e.id=ei.id and ei.birthday between ? and ?";

		Query query = session.createQuery(queryScript);
		query.setDate(0,birthdayFrom).setDate(1,birthdayTo);
		List resultList = query.list();
		if (resultList.size() == 0)
			throw new Exception("EmployeeNotFoundException");
		return resultList;
	}
	
	public static Employee getEmployeeByID(Long employeeID,Class employeeClass,Session session)throws Exception
	{
		Employee employee;
		Criteria criteria = session.createCriteria(Employee.class);
		Criterion criterion = Expression.eq("id",employeeID);
		criteria = criteria.add(criterion);
		List resultList = criteria.list();
		if (resultList.size() == 0)
			throw new Exception("EmployeeNotFoundException");
		else if (resultList.size() > 1)
			throw new Exception("EmployeeOverlappingException");
		if (employeeClass.equals(FrontDesk.class))
			employee = (FrontDesk)resultList.get(0);
		else if (employeeClass.equals(Manager.class))
			employee = (Manager)resultList.get(0);
		else
			employee = (SoftwareEngineer)resultList.get(0);
		return employee;
	}
	
	public static List getEmployeesByCondition(String sqlScript,Session session)throws Exception
	{
		Query query = session.createQuery(sqlScript);

		List resultList = query.list();
		return resultList;
	}

}
